import requests
from lxml import etree
# pip install pymysql
import pymysql

# 创建链接
con = pymysql.connect(host='127.0.0.1', user='root', password='1234', database='luncky')
# print(con)
# 设置字符编码
con.set_charset('utf8')

# 创建游标对象
cursor = con.cursor()


headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36',
}
url = 'http://www.cs.ecitic.com/newsite/cpzx/jrcpxxgs/zgcp/index.html'
res = requests.get(url, headers=headers)
html = res.content.decode()
# 进行xpath处理
tree = etree.HTML(html)
li_list = tree.xpath('//ul[@class="list-con"]/li')
# print(li_list)
for li in li_list:
    data = li.xpath('./span/text()')
    # 插入数据
    sql = f'insert into zhongxin(name, person,pingji,qidian, xinxi) values("{data[0]}", "{data[1]}", "{data[2]}", "{data[3]}", "{data[4]}")'
    # 下面的写法只有增删改才这样写
    try:
        cursor.execute(sql)
        # 写入到数据库  事务提交
        con.commit()
        print(sql, '插入成功')
    except Exception as err:
        print(sql, err)
        # 有错误则回滚  回到之前啥也没操作的状态
        con.rollback()



#  插入成功后进行查询
cursor.execute('select * from zhongxin')
# 查询多条
print(cursor.fetchall())
# 关闭连接
con.close()